<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8" >

<title>Mysql主从同步脚本 | 没想好的个人博客</title>

<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no">

<!-- <link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.7.2/css/all.css" integrity="sha384-fnmOCqbTlWIlj8LyTjo7mOUStjsKC4pOpQbqyi7RrhN7udi9RwhKkMHpvLbHG9Sr" crossorigin="anonymous"> -->
   <!-- Font Awesome CSS-->
   <link href="https://cdn.bootcss.com/font-awesome/5.13.0/css/all.css" rel="stylesheet">

   <link rel="stylesheet" href="https://cdn.bootcss.com/font-awesome/5.7.2/css/all.min.css" integrity="sha384-fnmOCqbTlWIlj8LyTjo7mOUStjsKC4pOpQbqyi7RrhN7udi9RwhKkMHpvLbHG9Sr" crossorigin="anonymous">

<link rel="shortcut icon" href="https://shixixiyue.gitee.io//favicon.ico?v=1709791195950">
<link rel="stylesheet" href="https://shixixiyue.gitee.io//styles/main.css">



<link rel="stylesheet" href="https://unpkg.com/aos@next/dist/aos.css" />
<script src="https://jsd.onmicrosoft.cn/npm/vue/dist/vue.js"></script>


<style>
  .hljs-ln{
    padding-bottom: 1.2rem;
    margin: 0rem !important;
  }
  .hljs-ln td {
    border: none !important;
    padding: 2px 6px !important;
    line-height: 1.2rem;
    font-size: 1rem;
  }
  .hljs-ln tr td:first-child  {
    color: cadetblue;
    border-right: 1px solid !important;
  }
  .hljs-ln tr {
    border: none !important;
  }
</style>

    <meta name="description" content="最近没啥可总结的，放点脚本以做备份






slaveMaster.sh  同步脚本 2.18
getMasterStatus.sh  得到主机日志节点的脚本 2.18
SyncTo18.sh 主备份还原到备的脚本 2.19
mysql..." />
    <meta name="keywords" content="其他" />
  </head>
  <body>
    <div id="app" class="main">

      <div class="sidebar" :class="{ 'full-height': menuVisible }">
  <div class="top-container" data-aos="fade-right">
    <div class="top-header-container">
      <a class="site-title-container" href="https://shixixiyue.gitee.io/">
        <img src="https://shixixiyue.gitee.io//images/avatar.png?v=1709791195950" class="site-logo">
        <h1 class="site-title">没想好的个人博客</h1>
      </a>
      <div class="menu-btn" @click="menuVisible = !menuVisible">
        <div class="line"></div>
      </div>
    </div>
    <div>
      
        
          <a href="https://shixixiyue.gitee.io/" class="site-nav">
            首页
          </a>
        
      
        
          <a href="https://shixixiyue.gitee.io//tag/4gOKry8Qv/" class="site-nav">
            .Net Core
          </a>
        
      
        
          <a href="https://shixixiyue.gitee.io//tag/njETBpqXu/" class="site-nav">
            FineUICore
          </a>
        
      
        
          <a href="https://shixixiyue.gitee.io//tag/XyXyeLOBB" class="site-nav">
            读书笔记
          </a>
        
      
        
          <a href="https://shixixiyue.gitee.io//tag/TyVX6hfCJ" class="site-nav">
            Vue笔记
          </a>
        
      
        
          <a href="https://shixixiyue.gitee.io//tag/3Hz2sPtzO/" class="site-nav">
            产品
          </a>
        
      
        
          <a href="https://shixixiyue.gitee.io//tag/Db1wShB_E" class="site-nav">
            其他
          </a>
        
      
        
          <a href="https://shixixiyue.gitee.io/" class="site-nav">
            一一一一一一一一一
          </a>
        
      
        
          <a href="https://shixixiyue.gitee.io//archives" class="site-nav">
            文章归档
          </a>
        
      
        
          <a href="https://shixixiyue.gitee.io//tags" class="site-nav">
            全部标签
          </a>
        
      
    </div>
  </div>
  <div class="bottom-container" data-aos="flip-up" data-aos-offset="0">
    <div class="social-container">
      
        
      
        
      
        
      
        
      
        
      
    </div>
    <div class="site-description">
      没事啥都说
    </div>
    <div class="site-footer">
      QQ：<a href="tencent://message/?uin=935732994&Site=''&Menu=yes">935732994<a/> <a href="http://beian.miit.gov.cn">京ICP备2024045283号<a/> | <a class="rss" href="https://shixixiyue.gitee.io//atom.xml" target="_blank">RSS</a>
    </div>
  </div>
</div>


      <div class="main-container">
        <div class="content-container" data-aos="fade-up">
          <div class="post-detail">
            <h2 class="post-title">Mysql主从同步脚本</h2>
            <div class="post-date">2023-09-14
            
                
                  <a href="https://shixixiyue.gitee.io/tag/Db1wShB_E/" class="tag">
                    其他
                  </a>
                
            </div>
            
            <div class="post-content" v-pre>
              <p>最近没啥可总结的，放点脚本以做备份</p>
<!-- more -->
<p><ul class="markdownIt-TOC">
<li>
<ul>
<li>
<ul>
<li><a href="#slavemastersh-%E5%90%8C%E6%AD%A5%E8%84%9A%E6%9C%AC-218">slaveMaster.sh  同步脚本 2.18</a></li>
<li><a href="#getmasterstatussh-%E5%BE%97%E5%88%B0%E4%B8%BB%E6%9C%BA%E6%97%A5%E5%BF%97%E8%8A%82%E7%82%B9%E7%9A%84%E8%84%9A%E6%9C%AC-218">getMasterStatus.sh  得到主机日志节点的脚本 2.18</a></li>
<li><a href="#syncto18sh-%E4%B8%BB%E5%A4%87%E4%BB%BD%E8%BF%98%E5%8E%9F%E5%88%B0%E5%A4%87%E7%9A%84%E8%84%9A%E6%9C%AC-219">SyncTo18.sh 主备份还原到备的脚本 2.19</a></li>
<li><a href="#mysqlbacksh-%E6%95%B0%E6%8D%AE%E5%BA%93%E5%A4%87%E4%BB%BD%E8%84%9A%E6%9C%AC-219">mysqlback.sh 数据库备份脚本 2.19</a></li>
<li><a href="#mysqlrestoresh-%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%98%E5%8E%9F%E8%84%9A%E6%9C%AC-218">mysqlRestore.sh 数据库还原脚本 2.18</a></li>
</ul>
</li>
</ul>
</li>
</ul>
</p>
<p>2.19 是主服务，2.18是备服务</p>
<div class="mermaid">
graph TB
    subgraph 2.19[2.19 主服务器]
        mysqlback[mysqlback.sh]
        SyncTo18[SyncTo18.sh]
    end
    subgraph 2.18[2.18 备服务器]
        slaveMaster[slaveMaster.sh] 
        getMasterStatus[getMasterStatus.sh]
        mysqlRestore[mysqlRestore.sh]
    end
    slaveMaster --> getMasterStatus
    slaveMaster --> SyncTo18[SyncTo18.sh]
    SyncTo18 --> mysqlback
    SyncTo18 --> mysqlRestore
    click SyncTo18 call gotoSyncTo18() "SyncTo18"
    click slaveMaster call gotoslaveMaster() "slaveMaster"
    click getMasterStatus call gotogetmasterstatus() "getMasterStatus"
    click mysqlback call gotomysqlback() "mysqlback"
    click mysqlRestore call gotomysqlRestore() "mysqlRestore"
</div>
<script>
function gotoSyncTo18() {document.getElementById('syncto18sh-主备份还原到备的脚本-219').scrollIntoView();}
function gotoslaveMaster() {document.getElementById('slavemastersh-同步脚本-218').scrollIntoView();}
function gotogetmasterstatus() {document.getElementById('getmasterstatussh-得到主机日志节点的脚本-218').scrollIntoView();}
function gotomysqlback() {document.getElementById('mysqlbacksh-数据库备份脚本-219').scrollIntoView();}
function gotomysqlRestore() {document.getElementById('mysqlrestoresh-数据库还原脚本-218').scrollIntoView();}
</script>
<h3 id="slavemastersh-同步脚本-218">slaveMaster.sh  同步脚本 2.18</h3>
<pre><code>#!/bin/bash
#完成主从同步的脚本

function log {
   Logpath=log$(date '+%Y%m%d').log
   echo &quot;$1&quot;
   echo &quot;$1&quot; &gt;&gt; $Logpath
}

username=root
password=root
masterip=192.168.2.19
masterserver=root@192.168.2.19


log &quot;1 主数据库锁定&quot;
mysql -u$username -p$password -h $masterip -e &quot;FLUSH TABLES WITH READ LOCK;&quot;

log &quot;2 从数据库停止同步&quot;
mysql -u$username -p$password -e &quot;STOP SLAVE;&quot;

log &quot;3 在从数据库还原主数据库的备份&quot;
ssh $masterserver &quot;/home/mysqlbackup/SyncTo18.sh&quot;

log &quot;4 从数据库重置同步&quot;
mysql -u$username -p$password -e &quot;RESET SLAVE;&quot;

log &quot;5 从数据库配置同步节点&quot;
output=$(/home/mysqlbackup/getMasterStatus.sh)
file=$(echo &quot;$output&quot; | awk 'NR==1{print $2}')
pos=$(echo &quot;$output&quot; | awk 'NR==2{print $2}')
mysql -u$username -p$password -e &quot;CHANGE MASTER TO MASTER_HOST='$masterip', MASTER_USER='$username', MASTER_PASSWORD='$password', MASTER_LOG_FILE='$file', MASTER_LOG_POS=$pos;&quot;

log &quot;6 从数据库开启同步&quot;
mysql -u$username -p$password -e &quot;START SLAVE;&quot;

log &quot;7 主数据库解除锁定&quot;
mysql -u$username -p$password -h $masterip -e &quot;UNLOCK TABLES;&quot;
</code></pre>
<h3 id="getmasterstatussh-得到主机日志节点的脚本-218">getMasterStatus.sh  得到主机日志节点的脚本 2.18</h3>
<pre><code>#!/bin/bash
# Connect to the MySQL master server and execute the SHOW MASTER STATUS command
output=$(mysql -uroot -proot -h 192.168.2.19 -e &quot;SHOW MASTER STATUS;&quot;)
# Extract the File and Position information from the command output using awk
file=$(echo &quot;$output&quot; | awk 'NR==2{print $1}')
pos=$(echo &quot;$output&quot; | awk 'NR==2{print $2}')
# Print the File and Position information
echo &quot;File: $file&quot;
echo &quot;Position: $pos&quot;

</code></pre>
<h3 id="syncto18sh-主备份还原到备的脚本-219">SyncTo18.sh 主备份还原到备的脚本 2.19</h3>
<pre><code>backup=&quot;/home/mysqlbackup&quot;
tablename=&quot;HDMES&quot;
if [ ! -d $backup  ];then
    mkdir $backup
    chmod 777 $backup
fi
name=$(date +%Y%m%d%H%M%S)$1_$tablename
/usr/bin/mysqldump  --default-character-set=utf8  $tablename &gt; $backup/$name.sql
echo &quot;———————本地备份完毕$name————————–&quot;
imageserver=root@192.168.2.18
ssh $imageserver &quot;/home/mysqlback.sh&quot;
echo &quot;——————执行远程备份数据库————————–&quot;
scp $backup/$name.sql $imageserver:$backup
echo &quot;———————推送完成————————–&quot;
ssh $imageserver &quot;/home/mysqlRestore.sh $backup/$name.sql&quot;
echo &quot;——————执行数据库还原完成————————–&quot;
</code></pre>
<h3 id="mysqlbacksh-数据库备份脚本-219">mysqlback.sh 数据库备份脚本 2.19</h3>
<pre><code>backup=&quot;/home/mysqlbackup&quot;
tablename=&quot;HDMES&quot;
if [ ! -d $backup  ];then
    mkdir $backup
    chmod 777 $backup
fi
sqlfile=$backup/$(date +%Y%m%d%H%M%S)$1_$tablename.sql
/usr/bin/mysqldump  --default-character-set=utf8  $tablename &gt; $sqlfile
cp $sqlfile /home/mysqlbackupshare/
</code></pre>
<h3 id="mysqlrestoresh-数据库还原脚本-218">mysqlRestore.sh 数据库还原脚本 2.18</h3>
<pre><code>username=&quot;root&quot; #用户名
password=&quot;root&quot; #密码
mysql -u$username -p$password HDMES&lt;$1
</code></pre>

            </div>
            
              <div class="tag-container">
                
                  <a href="https://shixixiyue.gitee.io/tag/Db1wShB_E/" class="tag">
                    其他
                  </a>
                
              </div>
            
            
              <div class="next-post">
                <div class="next">下一篇</div>
                <a href="https://shixixiyue.gitee.io/js-bi-bao-ying-yong/">
                  <h3 class="post-title">
                    JS闭包的应用实例
                  </h3>
                </a>
              </div>
            

            

          </div>

        </div>
      </div>
    </div>

    <script src="https://unpkg.com/aos@next/dist/aos.js"></script>
<script type="application/javascript">

AOS.init();

var app = new Vue({
  el: '#app',
  data: {
    menuVisible: false,
  },
})

</script>


  <script src="//jsd.onmicrosoft.cn/gh/highlightjs/cdn-release@11.5.1/build/highlight.min.js"></script>
  <script>
    hljs.initHighlightingOnLoad();
    //hljs.registerLanguage('mermaid', window.hljsDefineMermaid);
  </script>
  <script src="//jsd.onmicrosoft.cn/npm/highlightjs-line-numbers.js@2.8.0/dist/highlightjs-line-numbers.min.js"></script>
  <script>
    hljs.initLineNumbersOnLoad({
      singleLine: true
    });
  </script>
 <script src="https://jsd.onmicrosoft.cn/npm/mermaid/dist/mermaid.min.js"></script>
   <script>
    mermaid.initialize({
      startOnLoad: true,
      securityLevel: 'loose',
    });
  </script>





  </body>
</html>
